package net.comm.user.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class UserHomeServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from UserHome where seqID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into UserHome (  seqID,  userID,  work_location, work_sublocation,  work_area," +
				"  villageID, building,  houseNo,  fangchanNo, fcNoSet,  fcPicID, fcPicIDSts,  inTime,  inTimeSet," +
				"  famSts, famStsSet,  liveSts,  liveStsSet,  mtime  " +
				" ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update UserHome  set userID=?,  work_location=?, work_sublocation=?,  work_area=?," +
				"  villageID=?, building=?,  houseNo=?,  fangchanNo=?,  fcNoSet=?, fcPicID=?, fcPicIDSts=?,  inTime=?, " +
				" inTimeSet=?, famSts=?, famStsSet=?,  liveSts=?,  liveStsSet=?,  mtime=? " +
				"  where seqID=?";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdstsSql()
	{
		return "update UserHome  set  fcPicIDSts=? " +
				"  where seqID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select  seqID,  userID,  work_location, work_sublocation,  work_area," +
				"  villageID, building,  houseNo,  fangchanNo, fcNoSet,  fcPicID, fcPicIDSts,  inTime,  inTimeSet," +
				"  famSts, famStsSet,  liveSts,  liveStsSet,  mtime " +
				" from UserHome where seqID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select seqID,  userID,  work_location, work_sublocation,  work_area," +
				"  villageID, building,  houseNo,  fangchanNo, fcNoSet,  fcPicID, fcPicIDSts,  inTime,  inTimeSet," +
				"  famSts, famStsSet,  liveSts,  liveStsSet,  mtime " +
				" from UserHome");
		
		sb.append(" where UserID=?  Order by seqID desc ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID,  userID,  work_location, work_sublocation,  work_area," +
				"  villageID, building,  houseNo,  fangchanNo, fcNoSet,  fcPicID, fcPicIDSts,  inTime,  inTimeSet," +
				"  famSts, famStsSet,  liveSts,  liveStsSet,  mtime " +
				" from UserHome");
		
		sb.append(" where 1=1 ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyCtimeSql()
	{
	
		StringBuffer sb =  new StringBuffer("select h.seqID,  h.userID,  h.work_location, h.work_sublocation,  h.work_area," +
				"  h.villageID, h.building,  h.houseNo,  h.fangchanNo, h.fcNoSet, h.fcPicID, h.fcPicIDSts,  h.inTime,  h.inTimeSet," +
				"  h.famSts, h.famStsSet,  h.liveSts,  h.liveStsSet,  h.mtime " +
				" from UserHome h left join Users u on h.userID = u.userID ");
		
		sb.append(" where h.villageID = ?  and u.Ctime >= ?");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyNickSql()
	{
	
		StringBuffer sb =  new StringBuffer("select h.seqID,  h.userID,  h.work_location, h.work_sublocation,  h.work_area," +
				"  h.villageID, h.building,  h.houseNo,  h.fangchanNo, h.fcNoSet,  h.fcPicID, h.fcPicIDSts,  h.inTime,  h.inTimeSet," +
				"  h.famSts, h.famStsSet,  h.liveSts,  h.liveStsSet,  h.mtime " +
				" from UserHome h left join Userdetails u on h.userID = u.userID ");
		
		sb.append(" where u.Nick like ? group by h.userID Order by h.seqID ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID,  userID,  work_location, work_sublocation,  work_area," +
				"  villageID, building,  houseNo,  fangchanNo, fcNoSet,  fcPicID, fcPicIDSts,  inTime,  inTimeSet," +
				"  famSts, famStsSet,  liveSts,  liveStsSet,  mtime " +
				" from UserHome ");
				
		return sb.toString();	
	}	
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from UserHome";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from UserHome where UserID=? ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from UserHome h left join Users u on h.userID = u.userID " +
				"where villageID = ?  and u.Ctime >= ? ";
	}
	
	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT from UserHome h left join Userdetails u on h.userID = u.userID " +
				"where  u.Nick like ? ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserHomeerviceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
	
	
}
